package j.util.jdbc.simple.core;

import j.util.New;
import j.util.StringUtils;
import j.util.jdbc.simple.core.support.SqlUtils;
import j.util.jdbc.simple.metadata.Column;
import j.util.jdbc.simple.metadata.TableMetaData;

import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 更新操作的sql生成器
 * @author 刘军海
 * @version Sep 18, 2011 6:07:09 PM
 *
 */
public class SQLForUpdate {
	private TableMetaData tmd;
	public SQLForUpdate(TableMetaData tmd){
		this.tmd = tmd;
	}
	//按主键更新的where语句
	private String pkWhere;
	//按主键更新where语句的参数顺序
	private List<String> pkOrder;
	//由主键生成的set
	private Set<String> pkColumns;
	//更新前的检查本次更新是否合法的语句
	private String checkedSql;
	//检查参数顺序
	private List<String> checkedOrder;
	/**
	 * 根据参数生成update sql语句，where由pk指定
	 * @param updatedData
	 * @return
	 */
	public String generateUpdatedSqlByPk(Map<String,Object> updatedData,List<String> argsOrder){
		if(null==updatedData || updatedData.size()==0 || StringUtils.isNullOrEmpty(tmd.getPkColumns())){
			//没有指定pkcolumns，则返回空值
			return null;
		}
		String where = getPkWhere();
		StringBuilder sql = new StringBuilder();
		int i = 0;
		String dbColumn;
//		Map<String, String> entityAttributeColumnMap = tmd.getEntityAttributeColumnMap();
        Column column;
		for(Map.Entry<String, Object> entry : updatedData.entrySet()){
			dbColumn = entry.getKey();//entityAttributeColumnMap.get(entry.getKey().toUpperCase());
			if(StringUtils.isNullOrEmpty(dbColumn)){
				continue;
			}
            column = tmd.getColumn(dbColumn);
            if(null == column || !column.isUpdatable()){
                continue;
            }
			if(pkColumns.contains(dbColumn)){
				continue;
			}
			if(i++>0){
				sql.append(",");
			}
			sql.append(dbColumn).append("=").append("?");
			argsOrder.add(dbColumn);
		}
		if(i>0){
			sql.append(" WHERE ").append(where);
			String _sql = "UPDATE "+tmd.getTableName()+" SET ";
			_sql += sql.toString();
			
			argsOrder.addAll(pkOrder);
			return _sql;
		}
		return null;
		
	}
	/**
	 * 对比old数据，仅更新变化的数据，即与old一样的数据则不在更新列表中
	 * updatedData与old map中的key均为数据库中字段名称
	 * @param updatedData
	 * @param old
	 * @param argsOrder
	 * @return
	 */
	public String generateUpdatedSqlWithComparedOld(Map<String,Object> updatedData,Map<String,Object> old,List<String> argsOrder){
		if(null==updatedData || updatedData.size()==0 || StringUtils.isNullOrEmpty(tmd.getPkColumns())){
			//没有指定pkcolumns，则返回空值
			return null;
		}
		String where = getPkWhere();
		StringBuilder sql = new StringBuilder();
		int i = 0;
		String dbColumn;
		Object newV,oldV;
		Set<String> usedColumns = tmd.getUpdateAttribute().getUsedColumns();
        Column column;
		for(Map.Entry<String, Object> entry : updatedData.entrySet()){
			dbColumn = entry.getKey();
			if(StringUtils.isNullOrEmpty(dbColumn)){
				continue;
			}
            column = tmd.getColumn(dbColumn);
            if(null == column || !column.isUpdatable()){
                continue;
            }
			if(pkColumns.contains(dbColumn)){
				continue;
			}
			if(null!=usedColumns && !usedColumns.contains(dbColumn)){
				continue;
			}
			newV=entry.getValue();
			oldV = old.get(dbColumn);
			if(null==newV && null == oldV){
				//数据库中与指定的新值都为null，则过
				continue;
			}
			//比较新旧值，若一致，则过滤
			if(null!=newV && null != oldV && newV.equals(oldV)){
				continue;
			}
			if(i++>0){
				sql.append(",");
			}
			sql.append(dbColumn).append("=").append("?");
			argsOrder.add(dbColumn);
		}
		if(i>0){
			sql.append(" WHERE ").append(where);
			argsOrder.addAll(pkOrder);
			String _sql = "UPDATE "+tmd.getTableName()+" SET ";
			_sql += sql.toString();
			return _sql;
		}
		return null;
	}
	/**
	 * 以自定义方式生成更新条件
	 * @param updatedData
	 * @param where
	 * @param argsOrder
	 * @return
	 */
	public String generateUpdatedSqlByCW(Map<String, Object> updatedData,
			Map<String, Object> where,List<String> argsOrder){
		String sql = generateUpdatedSqlByUpdatedMap(updatedData,argsOrder);
		if(StringUtils.isNullOrEmpty(sql)){
			return null;
		}
		sql = "UPDATE "+tmd.getTableName()+" SET "+sql;
		String _where = SqlUtils.fromArgsToWhereSql(tmd.getEntityAttributeColumnMap(), where, argsOrder);
		if(StringUtils.isNotEmpty(_where)){
			sql += " WHERE "+_where;
		}
		return sql;
	}
	private String generateUpdatedSqlByUpdatedMap(Map<String, Object> updatedData,List<String> argsOrder){
		StringBuilder sql = new StringBuilder();
		int i = 0;
		String dbColumn;
		Map<String, String> entityAttributeColumnMap = tmd.getEntityAttributeColumnMap();
        Column column;
		for(Map.Entry<String, Object> entry : updatedData.entrySet()){
			dbColumn = entityAttributeColumnMap.get(entry.getKey().toUpperCase());
			if(StringUtils.isNullOrEmpty(dbColumn)){
				continue;
			}
            column = tmd.getColumn(dbColumn);
            if(null == column || !column.isUpdatable()){
                continue;
            }
			if(getPkColumns().contains(dbColumn)){
				continue;
			}
			if(i++>0){
				sql.append(",");
			}
			sql.append(dbColumn).append("=").append("?");
			argsOrder.add(dbColumn);
		}
		return sql.toString();
	}
	/**
	 * 获取更新前的检查操作sql语句，同时生成该语句的参数顺序
	 * @return
	 */
	public String getCheckedSql(){
		if(tmd.getUpdateAttribute().isChecked() 
				&& StringUtils.isNullOrEmpty(checkedSql)
				&& (null!=tmd.getUkColumns() && tmd.getUkColumns().size()>0)){
			synchronized(this){
				if(StringUtils.isNullOrEmpty(checkedSql)){
					_createCheckedSql();
				}
			}
		}
		return checkedSql;
	}
	
	private void _createCheckedSql() {
		if(null==tmd.getUkColumns() || tmd.getUkColumns().size()==0){
			return;
		}
		StringBuilder sql = new StringBuilder("SELECT COUNT(1) FROM ");
		sql.append(tmd.getTableName());
		checkedOrder = New.list();
		//修改检测重复时,应将主键一起
        Set<String> set = StringUtils.asLSet(tmd.getPkColumns(), ",");
        int i = 0;
        if(set.size()>0){
            sql.append(" WHERE ");
            for(String pk : set){
                if(i++>0){
                    sql.append(" AND ");
                }
                sql.append(pk).append(" !=? ");
                checkedOrder.add(tmd.getPkColumns());
            }
        }
        if(i == 0){
            sql.append(" WHERE ");
        }else{
            sql.append(" AND ");
        }
        String s = SqlUtils.fromColumnsToWhereSqlForDMLCheck(tmd.getUkColumns(),
                this.checkedOrder);
		if(s.length()>0){
			sql.append("(").append(s).append(")");
		}
		checkedSql = sql.toString();
	}

	/**
	 * 生成pk where，同时生成这部分的参数顺序，并同时生成主键的set列表
	 * @return
	 */
	private String getPkWhere() {
		if(StringUtils.isNullOrEmpty(pkWhere)){
			synchronized(this){
				if(StringUtils.isNullOrEmpty(pkWhere)){
					pkOrder= New.list(4);
					pkColumns = New.set(4);
					pkWhere = SqlUtils.fromCompositeColumnsToWhereSql(tmd.getPkColumns(), pkOrder);
					for(String s : pkOrder){
						pkColumns.add(s);
					}
				}
			}
		}
		return pkWhere;
	}
	private Set<String> getPkColumns(){
		if(null == pkColumns){
			synchronized(this){
				if(null == pkColumns){
					getPkWhere();
				}
			}
		}
		return pkColumns;
	}
	public List<String> getCheckedOrder() {
		return checkedOrder;
	}
	/**
	 * 生成获取旧值的sql语句
	 * @return
	 */
	public String generateGetOldDataSql(){
		String where = getPkWhere();
		String sql = "SELECT * FROM "+tmd.getTableName()+" WHERE "+where;
		return sql;
	}
	public List<String> getPkOrder() {
		return pkOrder;
	}
}
